/*
SELECT * FROM fn_load_customer_all_contact_persons_details(153)
*/
alter function fn_load_customer_all_contact_persons_details ( @customer_id int )
RETURNS @fn_results Table ( Customer_Id int, Contact_Person varchar(50), Email varchar(50), Contact_Person_Type varchar(50), Display_Name varchar(50), Sales_Ack_Mail bit, Ship_Alerts bit, Proforma_Invoice_Mail bit, Packing_Slip_Mail bit, Comm_Invoice_Mail bit, Quote_Ack_Mail bit, RMA_ACK_MAIL BIT )
AS
BEGIN
	declare @customer_name varchar(65)

	declare @emailCursor CURSOR

 	declare @results Table ( Customer_Id int, Contact_Person varchar(50), Email varchar(50), Contact_Person_Type varchar(50), Display_Name varchar(50), Sales_Ack_Mail bit, Ship_Alerts bit, Proforma_Invoice_Mail bit, Packing_Slip_Mail bit, Comm_Invoice_Mail bit, Quote_Ack_Mail bit, RMA_ACK_MAIL BIT )
 	
	SELECT @customer_name = C_Name FROM Customer WHERE Customer_ID = @customer_id

	--DECLARE @PRINT_MSG VARCHAR (200)
	--SET @PRINT_MSG = ' @customer_id = ' + CONVERT(VARCHAR,@customer_id)+ ' @customer_name = ' + CONVERT(VARCHAR,@customer_name) + ' @shipping_address_id = ' + CONVERT(VARCHAR,@shipping_address_id) + ' @billing_address_id = ' + CONVERT(VARCHAR,@billing_address_id)

	---LOAD CUSTOMER CONTACT PERSON EMAIL
	INSERT INTO @results SELECT @customer_id, Contact_Person, Email, 'CUSTOMER-CONTACT', 'Customer Contact Person', Sales_Ack_Mail, Ship_Alerts, Proforma_Invoice_Mail, Packing_Slip_Mail, Comm_Invoice_Mail, Quote_Ack_Mail, RMA_ACK_MAIL 
	FROM Customer_Contact_Persons_Table WHERE  ( Email IS NOT NULL ) AND ( Email <> '' ) AND ( ISNULL( Deactivated, 0)  <> 1 ) AND ( Customer_Id = @customer_id )

	--LOAD CUSTOMER BILLING CONTACT PERSON EMAIL
	INSERT INTO @results SELECT @customer_id, Contact_Person, Email, 'BILLING-CONTACT', 'Billing Contact Person', Sales_Ack_Mail, Ship_Alerts, Proforma_Invoice_Mail, Packing_Slip_Mail, Comm_Invoice_Mail, Quote_Ack_Mail, RMA_ACK_MAIL 
	FROM Customer C INNER JOIN Address_Contact_Persons_Table ACPT ON C.C_Default_Billing_Address_ID = ACPT.Address_Id WHERE ( Email IS NOT NULL ) AND ( Email <> '' ) AND ( ISNULL( Deactivated, 0)  <> 1 ) AND ( C.Customer_ID = @customer_id )
		
	--LOAD CUSTOMER SHIPPING CONTACT PERSON EMAIL
	INSERT INTO @results SELECT @customer_id, Contact_Person, Email, 'SHIPPING-CONTACT', 'Shipping Contact Person', Sales_Ack_Mail, Ship_Alerts, Proforma_Invoice_Mail, Packing_Slip_Mail, Comm_Invoice_Mail, Quote_Ack_Mail, RMA_ACK_MAIL 
	FROM Customer C INNER JOIN Address_Contact_Persons_Table ACPT ON C.C_Default_Shipping_Address_ID = ACPT.Address_Id WHERE ( Email IS NOT NULL ) AND ( Email <> '' ) AND ( ISNULL( Deactivated, 0)  <> 1 ) AND ( C.Customer_ID = @customer_id ) 
	
	insert @fn_results select * from @results

 	return

end
 